package com.shuohe.util.db;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DbQuerySql {

	
//	public static void main(String[] argv)
//	{
//		debug(andDateAGreatThanB("a","2017-06-22"));
//	}
	
	private static void debug(String s)
	{
		System.out.println(s);
	}
	
	/**
	 * a����b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:42 
	 * @param a
	 * @param b
	 * @return
	 */
//	public static String andAGreaterThanB(String a,String b)
//	{		
//        if(b!=null&&b.length()!=0)
//        {
//        	return " and "+a+">"+"\'"+b+"\'";
//        } 
//        return "";
//	}

	/**
	 * a=b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:58 
	 * @param a
	 * @param b
	 * @return
	 */
	public static String andAEqualToB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " and "+a+"="+"\'"+b+"\'";
        } 
        return "";
	}
	
	/**
	 * a = b b可以为空
	 * @param a
	 * @param b
	 * @return
	 */
	public static String andAEqualToBNecessary(String a,String b)
	{		
        if(b==null||b.length()==0)
        {
        	return " and "+a+"="+"\'"+"\'";
        } 
        return " and "+a+"="+"\'"+b+"\'";
	}
	
	
	public static String andAEqualToB(String a,int b)
	{		
        	return " and "+a+"="+b;
	}
	public static String andAEqualToB(int a,int b)
	{		
        	return " and "+"\'"+a+"\'"+"="+b;
	}
	
	public static String andADontEqualToB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " and "+a+"!="+"\'"+b+"\'";
        } 
        else
        {
        	return " and "+a+"!="+"\'"+"\'";
        }
	}
	
	
	public static String orAEqualToB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " or "+a+"="+"\'"+b+"\'";
        } 
        return "";
	}
	
	/**
	 * a!=b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:58 
	 * @param a
	 * @param b
	 * @return
	 */
	public static String andANoEqualToB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " and "+a+"!="+"\'"+b+"\'";
        } 
        return "";
	}
	
	
	/**
	 * a����b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:42 
	 * @param a
	 * @param b
	 * @return
	 */
	public static String WhereAGreaterThanB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " where "+a+">"+"\'"+b+"\'";
        } 
        return "";
	}
	/**
	 * aС��b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:50 
	 * @param a
	 * @param b
	 * @return
	 */
	public static String WhereALessThanB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " where "+a+"<"+"\'"+b+"\'";
        } 
        return "";
	}
	/**
	 * a=b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:58 
	 * @param a
	 * @param b
	 * @return
	 */
	public static String WhereAEqualToB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " where "+a+"="+"\'"+b+"\'";
        } 
        return "";
	}
	
	
	
	public static String andAGreaterThanB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " and "+a+">="+"\'"+b+"\'";
        } 
        return "";
	}
	public static String andAGreaterThanBNecessary(String a,String b)
	{		
        if(b==null || b.length()==0)
        {
        		return " and "+a+">="+"\'"+"\'";
        } 
        return " and "+a+">="+"\'"+b+"\'";
	}
	public static String aGreaterThanB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " "+a+">="+"\'"+b+"\'";
        } 
        return "";
	}	
	public static String andALessThanB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " and "+a+"<="+"\'"+b+"\'";
        } 
        return "";
	}
	public static String andALessThanBNecessary(String a,String b)
	{		
		if(b==null || b.length()==0)
        {
        	return " and "+a+"<="+"\'"+"\'";
        } 
		return " and "+a+"<="+"\'"+ b +"\'";
	}
	public static String aLessThanB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " "+a+"<="+"\'"+b+"\'";
        } 
        return "";
	}	
	/**
	 * aС��b
	 * @author    ������
	 * @date      2017��2��9�� ����9:17:50 
	 * @param a
	 * @param b
	 * @return
	 */
//	public static String andALessThanB(String a,String b)
//	{		
//        if(b!=null&&b.length()!=0)
//        {
//        	return " and "+a+"<"+"\'"+b+"\'";
//        } 
//        return "";
//	}
	public static String AEqualToB(String a,String b)
	{		
        if(b!=null&&b.length()!=0)
        {
        	return " "+a+"="+"\'"+b+"\'";
        } 
        return "";
	}
	public static String AEqualToB(String a,int b)
	{		
        	return " "+a+"="+"\'"+b+"\'";
	}
	
	public static String andALikeB(String a,String b)
	{
		if(b==null||b.length()==0)
			return "";
		if(a==null||a.length()==0)
			return "";
		
		return " and "+a+" like "+"\'%"+b+"%\'";
	}
	public static String aLikeB(String a,String b)
	{
		if(b==null||b.length()==0)
			return "";
		if(a==null||a.length()==0)
			return "";
		
		return " "+a+" like "+"\'%"+b+"%\'";
	}
	
	public static String limit(int page,int rows)
	{
		return " limit "+(page-1)*rows+","+rows;
	}
	
	public static String limit(String page,String rows)
	{
		if(page==null||page.length()==0)
			return "";
		if(rows==null||rows.length()==0)
			return "";
		
		int pageNum = 0;
		int rowNum = 0;
		
		try
		{
			pageNum = Integer.parseInt(page);
			rowNum = Integer.parseInt(rows);	
			return " limit "+(pageNum-1)*rowNum+","+rowNum;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return "";
		}
	}
	
	
	private enum DateStatus{
		NONE,
		ERROR,
		YYMMDDHHMMSS,
		YYMMDD
	}
	/**
	 * ʱ��Ƚ�,A����B
	 * @author    ������
	 * @date      2017��6��1�� ����10:56:41 
	 * @param A
	 * @param B
	 * @return
	 */
	public static String andDateAGreatThanB(String A,String B)
	{		
		if(B!=null&&B.length()!=0)
		{
			//1.�ж������Ƿ���ʱ������
			DateStatus flag = DateStatus.NONE;
		    try {
		    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				simpleDateFormat.parse(B);
				flag = DateStatus.YYMMDDHHMMSS;
			} catch (ParseException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
			    try {
			    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
					simpleDateFormat.parse(B);
					flag = DateStatus.YYMMDD;
				} catch (ParseException e1) {
					// TODO Auto-generated catch block
//					e1.printStackTrace();
					debug("DbQuerySql andDateAGreatThanB ʱ��ת������");
				}  
			}  
		    		   
		    debug("flag = "+flag);
		    if(flag == DateStatus.YYMMDDHHMMSS)
		    {
		    	return andAGreaterThanB(A, B);
		    }
		    if(flag == DateStatus.YYMMDD)
		    {
		    	//return andAGreaterThanB(A, B+" 00:00:00");
		    	return andAGreaterThanB(A, B);
		    }		    		
		}
		return "";
	}
	/**
	 * 处理日期格式的数据，不包括时分秒
	 * @param A
	 * @param B
	 * @return
	 */
	public static String andDateAGreatThanB1(String A,String B)
	{		
		if(B!=null&&B.length()!=0)
		{
			return andAGreaterThanB(A, B);		
		}
		return "";
	}
	public static String andDateAGreatThanBNecessary(String A,String B)
	{		
		if(B!=null&&B.length()!=0)
		{
			DateStatus flag = DateStatus.NONE;
		    try {
		    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				simpleDateFormat.parse(B);
				flag = DateStatus.YYMMDDHHMMSS;
			} catch (ParseException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
			    try {
			    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
					simpleDateFormat.parse(B);
					flag = DateStatus.YYMMDD;
				} catch (ParseException e1) {
					// TODO Auto-generated catch block
//					e1.printStackTrace();
				}  
			}  
		    debug("flag = "+flag);
		    if(flag == DateStatus.YYMMDDHHMMSS)
		    {
		    		return andAGreaterThanBNecessary(A, B);
		    }
		    if(flag == DateStatus.YYMMDD)
		    {
		    		return andAGreaterThanBNecessary(A, B+" 00:00:00");
		    }		    		
		}
		return "";
	}	
	/**
	 * ʱ��Ƚϣ�AС��B
	 * @author    ������
	 * @date      2017��6��1�� ����11:03:56 
	 * @param A
	 * @param B
	 * @return
	 */
	public static String andDateALessrThanB(String A,String B)
	{		
		if(B!=null&&B.length()!=0)
		{
			//1.�ж������Ƿ���ʱ������
			DateStatus flag = DateStatus.NONE;
		    try {
		    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				simpleDateFormat.parse(B);
				flag = DateStatus.YYMMDDHHMMSS;
			} catch (ParseException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
			    try {
			    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
					simpleDateFormat.parse(B);
					flag = DateStatus.YYMMDD;
				} catch (ParseException e1) {
					// TODO Auto-generated catch block
//					e1.printStackTrace();
					debug("DbQuerySql andDateALessrThanB ʱ��ת������");
				}  
			}  
		    		    		    
		    if(flag == DateStatus.YYMMDDHHMMSS)
		    {
		    	return andALessThanB(A, B);
		    }
		    if(flag == DateStatus.YYMMDD)
		    {
		    	//return andALessThanB(A, B+" 24:60:60");
		    	return andALessThanB(A, B);
		    }		    		
		}
		return "";
	}
	public static String andDateALessrThanBNecessary(String A,String B)
	{		
		if(B!=null&&B.length()!=0)
		{
			//1.�ж������Ƿ���ʱ������
			DateStatus flag = DateStatus.NONE;
		    try {
		    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
				simpleDateFormat.parse(B);
				flag = DateStatus.YYMMDDHHMMSS;
			} catch (ParseException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
			    try {
			    	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
					simpleDateFormat.parse(B);
					flag = DateStatus.YYMMDD;
				} catch (ParseException e1) {
					// TODO Auto-generated catch block
//					e1.printStackTrace();
					debug("DbQuerySql andDateALessrThanB ʱ��ת������");
				}  
			}  
		    		    		    
		    if(flag == DateStatus.YYMMDDHHMMSS)
		    {
		    	return andALessThanBNecessary(A, B);
		    }
		    if(flag == DateStatus.YYMMDD)
		    {
		    	return andALessThanBNecessary(A, B+" 24:60:60");
		    }		    		
		}
		return "";
	}	
	
	/**
	 * ��ȡʱ�䷶Χ�ڵ�sql���
	 * @author    ������
	 * @date      2017��6��3�� ����10:56:31 
	 * @param target_date
	 * @param start_date
	 * @param end_date
	 * @return
	 */
	public static String andDataLimit(String target_date,String start_date,String end_date)
	{
		String ret = ""; 
		ret += andDateAGreatThanB(target_date,start_date);
		ret += andDateALessrThanB(target_date,end_date);
		return ret;
	}
	
	
	
}
